package co.baiku.boot.office;

import co.baiku.boot.common.tools.ObjectTools;
import co.baiku.boot.common.tools.ReflectTools;
import co.baiku.boot.common.tools.StringTools;
import co.baiku.boot.common.type.TimeFormat;
import co.baiku.boot.office.annotaion.Office;
import co.baiku.boot.office.annotaion.OfficeType;
import co.baiku.boot.office.excel.annotation.ExcelAnnotaionReader;
import co.baiku.boot.office.excel.annotation.ExcelColumn;
import co.baiku.boot.office.excel.annotation.ExcelSheet;
import co.baiku.boot.office.excel.exception.ExcelAnnotationException;
import co.baiku.boot.office.excel.model.CellExpression;
import co.baiku.boot.office.excel.model.ExcelColumnModel;
import co.baiku.boot.office.excel.model.ExcelSheetModel;
import co.baiku.boot.office.files.ExcelFile;
import co.baiku.boot.office.vo.CellInfo;
import co.baiku.boot.office.vo.RowInfo;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.expression.Expression;
import org.springframework.expression.ExpressionParser;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.springframework.expression.spel.support.StandardEvaluationContext;

import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.function.Function;

/**
 * 将Excel类导出到文件
 *
 * <p>
 * 1.Excel类指的添加了类注解@Excel<br>
 * 2.Excel类结构和Excel文档类似，成员变量为@ExcelSheet，即包含多个工作簿<br>
 * 3.@ExcelSheet是工作簿模型，是一个List&lt;T&gt;<br>
 * </p>
 */
public class Excel {
    private static ExpressionParser parser = new SpelExpressionParser();
    private static Logger log = LoggerFactory.getLogger(Excel.class);

    public static Workbook openExcel(File file) throws IOException {
        if (file == null) {
            return null;
        }
        String[] fileNameArr = file.getName().split("\\.");
        String ext = fileNameArr[fileNameArr.length - 1];
        OfficeType officeType;
        if ("xlsx".equalsIgnoreCase(ext)) {
            officeType = OfficeType.xlsx;
        } else {
            officeType = OfficeType.xls;
        }
        return openExcel(new FileInputStream(file), officeType);
    }

    public static Workbook openExcel(InputStream input, OfficeType officeType) throws IOException {
        Workbook workbook;
        if (officeType == OfficeType.xlsx) {
            workbook = new XSSFWorkbook(input);
        } else {
            workbook = new HSSFWorkbook(new POIFSFileSystem(input));
        }
        return workbook;
    }

    public static Sheet openExcel(File file, int sheetIndex) throws IOException {
        Workbook workbook = openExcel(file);
        if (workbook == null) {
            return null;
        }
        return workbook.getSheetAt(sheetIndex);
    }

    public static String getCellValue(Sheet sheet, int rowAt, int cellAt) {
        Row row = sheet.getRow(rowAt);
        return getCellValue(row, cellAt);
    }

    public static String getCellValue(Row row, int cellAt) {
        Cell cell = row.getCell(cellAt);
        if (cell == null) {
            return null;
        }
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }

    public static Integer getCellValueInteger(Row row, int cellAt) {
        Cell cell = row.getCell(cellAt);
        if (cell == null) {
            return null;
        }
        cell.setCellType(CellType.STRING);
        return Integer.valueOf(cell.getStringCellValue());
    }


    public static Integer getCellValueInteger(Sheet sheet, int rowAt, int cellAt) {
        return getCellValueInteger(sheet.getRow(rowAt), cellAt);
    }

    public static <T> List<T> readExcel(Workbook workbook, int sheetAt, Class<T> sheetClass) throws Exception {
        return readExcel(workbook, sheetAt, sheetClass, null);
    }

    /**
     * 单个sheet的解析
     *
     * @param workbook
     * @param sheetAt
     * @param sheetClass
     * @return
     * @throws Exception
     */
    public static <T> List<T> readExcel(Workbook workbook, int sheetAt, Class<T> sheetClass, Function<RowInfo, Boolean> rowCheck) throws Exception {
        Sheet sheet = workbook.getSheetAt(sheetAt);
        ExcelColumnModel cm = ExcelAnnotaionReader.readColumnModel(sheetClass);
        // 2.2.2取得列数
        int columnCount = cm.getModelMap().keySet().stream().max(Comparator.naturalOrder()).get() + 1;
        //忽略表头
        Iterator<Row> rowsIter = sheet.rowIterator();

        List<T> resultData = new ArrayList<>();
        boolean title = false;
        while (rowsIter.hasNext()) {
            Row row = rowsIter.next();
            if (!title) {
                title = true;
                continue;
            }
            RowInfo rowInfo = new RowInfo(row);
            T entity = sheetClass.newInstance();
            boolean emptyRow = true;
            for (int j = 0; j < columnCount; j++) {
                if (cm.getIndex(j) == null) {
                    continue;
                }
                ExcelColumn excelColumn = cm.getIndex(j).values().iterator().next();
                Cell cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                // 取得对应的类型
                Class<?> filedType = excelColumn.type();
                // 取得属性名称
                String filed = cm.getIndex(j).keySet().iterator().next();
                Object value = null;
                if (filedType == String.class) {
                    // 字符串
                    cell.setCellType(CellType.STRING);
                    value = cell.getStringCellValue();
                } else if (filedType == Integer.class && cell.getCellType() != CellType.BLANK) {
                    // 整型
//                    cell.setCellType(CellType.NUMERIC);
                    value = (int) cell.getNumericCellValue();
                } else if (filedType == Long.class && cell.getCellType() != CellType.BLANK) {
                    // 长整型
                    cell.setCellType(CellType.NUMERIC);
                    value = (long) cell.getNumericCellValue();
                } else if (filedType == BigDecimal.class && cell.getCellType() != CellType.BLANK) {
                    // 长整型
                    cell.setCellType(CellType.NUMERIC);
                    value = BigDecimal.valueOf(cell.getNumericCellValue());
                } else if ((filedType == Double.class
                        || filedType == Float.class) && cell.getCellType() != CellType.BLANK) {
                    // 双精度或浮点型
                    cell.setCellType(CellType.NUMERIC);
                    value = cell.getNumericCellValue();
                } else if (filedType == Date.class) {
                    // 日期
                    if (cell.getCellTypeEnum() == CellType.STRING) {
                        String cellValue = cell.getStringCellValue();
                        try {
                            value = new SimpleDateFormat(StringTools.nvl(excelColumn.format(), TimeFormat.DATETIME.getPattern())).parse(cellValue);
                        } catch (ParseException e) {
                        }
                    } else {
                        cell.setCellType(CellType.NUMERIC);
                        value = cell.getDateCellValue();
                    }
                } else if (filedType == LocalDateTime.class) {
                    if (cell.getCellTypeEnum() == CellType.STRING) {
                        String cellValue = cell.getStringCellValue();
                        try {
                            value = TimeFormat.toTime(cellValue, StringTools.nvl(excelColumn.format(), TimeFormat.DATETIME.getPattern()));
                        } catch (Exception e) {
                        }
                    } else {
                        cell.setCellType(CellType.NUMERIC);
                        value = ObjectTools.getIfNotNull(cell.getDateCellValue(), TimeFormat::fromDate);
                    }
                } else if (filedType == LocalDate.class) {
                    if (cell.getCellTypeEnum() == CellType.STRING) {
                        String cellValue = cell.getStringCellValue();
                        if (StringTools.isNotBlank(cellValue)) {
                            try {
                                value = TimeFormat.toDate(cellValue, StringTools.nvl(excelColumn.format(), TimeFormat.DATE.getPattern()));
                            } catch (Exception e) {
                            }
                        }
                    } else {
                        cell.setCellType(CellType.NUMERIC);
                        value = ObjectTools.getIfNotNull(cell.getDateCellValue(), item -> TimeFormat.fromDate(item).toLocalDate());
                    }
                }
                if (ObjectTools.isNotBlank(value)) {
                    ReflectTools.setValueByName(filed, value, entity);
                    emptyRow = false;
                }
                rowInfo.getCells().put(filed, new CellInfo(cell, filed, value, filedType));
            }
            if (!emptyRow) {
                resultData.add(entity);
            }
            if (rowCheck != null && !rowCheck.apply(rowInfo)) {
                break;
            }
        }
        return resultData;
    }

    /**
     * 整个文件的解析
     *
     * @param file
     * @param clazz
     * @return
     * @throws Exception
     */
    public static <T extends ExcelFile> T readExcel(File file, Class<T> clazz) throws Exception {
        Workbook workbook = openExcel(file);
        ExcelAnnotaionReader reader = new ExcelAnnotaionReader(clazz);
        int sheetCount = reader.getSheetMap().size();
        if (sheetCount == 0) {
            throw new ExcelAnnotationException("请至少创建一个工作簿（Sheet）,在Excel模型类中添加注解@ExcelSheet创建");
        }
        int sheetIndex = 0;
        T t = clazz.newInstance();
        for (Map.Entry<Class<?>, ExcelSheetModel> entry : reader.getSheetMap().entrySet()) {
            Class<?> clazzItem = entry.getKey();
            ExcelSheetModel sheetModel = entry.getValue();
            Map<Integer, Field> sheetFields = sheetModel.getSheetFields();
            Field field = sheetFields.get(sheetIndex);
            List<?> resultData = readExcel(workbook, sheetIndex, clazzItem);
            ReflectTools.setValueByName(field, resultData, t);
        }
        return t;
    }

    /**
     * 注解形式导出excel文件
     *
     * @param data 数据
     * @param out  输出流
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     * @throws ExcelAnnotationException
     */
    public static void saveExcel(ExcelFile data, OutputStream out) throws IllegalAccessException, ExcelAnnotationException {
        ExcelAnnotaionReader reader = new ExcelAnnotaionReader(data.getClass());
        // 1.获得文档类型和保存路径
        Office office = reader.getOffice();
        // 文档类型
        OfficeType officeType = office.type();
        // 1.1根据文档类型生成WorkBook
        Workbook workbook;
        if (officeType == OfficeType.xlsx) {
            workbook = new XSSFWorkbook();
        } else {
            workbook = new HSSFWorkbook();
        }
        // 工作簿列数
        int columnCount = 0;
        // 2.获得Sheet工作簿
        // 2.1取得数量
        int sheetCount = reader.getSheetMap().size();
        if (sheetCount == 0) {
            throw new ExcelAnnotationException("请至少创建一个工作簿（Sheet）,在Excel模型类中添加注解@ExcelSheet创建");
        }
        // 2.2根据工作簿数量和名称生成对应的工作簿
        for (int i = 0; i < sheetCount; i++) {
            for (Class<?> clazz : reader.getSheetMap().keySet()) {
                Map<String, ExcelSheet> sheetModel = reader.getSheetMap()
                        .get(clazz).getIndex(i);
                if (sheetModel == null) {
                    // 为空时表明遍历的不是当前sheet
                    continue;
                }
                String filedName = sheetModel.keySet().iterator().next();
                ExcelSheet excelSheet = sheetModel.values().iterator()
                        .next();
                Sheet sheet = workbook.createSheet(excelSheet.name());
                // 2.2.1获得列模型
                ExcelColumnModel cm = reader.getColumnMap().get(clazz);
                if (cm == null) {
                    throw new ExcelAnnotationException("列数为0，在sheet模型类中添加注解@ExcelColumn创建列");
                }
                // 2.2.2取得列数
                columnCount = cm.getModelMap().size();
                // 2.2.3创建表头
                Row titleRow = sheet.createRow(0);
                // 2.2.3.1 在索引0的位置创建单元格（左上端）
                for (int j = 0; j < columnCount; j++) {
                    // 取得对应index的注解
                    ExcelColumn excelColumn = cm.getIndex(j).values()
                            .iterator().next();
                    Cell cell = titleRow.createCell((short) j);
                    // 设定类型为字符串
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(excelColumn.name());
                }
                // 2.2.4对每一列设值(注意行加1)
                // 2.2.4.1获得sheet中所有行的值
                List<?> rowList = (List<?>) ReflectTools.getValueByName(filedName, data);
                if (rowList == null) {
                    continue;
                }
                for (int k = 1; k < rowList.size() + 1; k++) {
                    Object o = rowList.get(k - 1);
                    Row row = sheet.createRow(k);
                    for (int j = 0; j < columnCount; j++) {
                        // 取得对应index的注解
                        ExcelColumn excelColumn = cm.getIndex(j).values().iterator().next();
                        Cell cell = row.createCell((short) j);
                        // 取得对应的类型
                        Class<?> filedType = excelColumn.type();
                        // 取得属性名称
                        String filed = cm.getIndex(j).keySet().iterator()
                                .next();
                        Object value = ReflectTools.getValueByName(filed, o);
                        if (filedType == String.class) {
                            // 字符串
                            cell.setCellType(CellType.STRING);
                            if (value == null) {
                                cell.setCellValue("");
                            } else {
                                cell.setCellValue(value.toString());
                            }
                        } else if (filedType == Integer.class) {
                            // 整型
                            cell.setCellType(CellType.NUMERIC);
                            if (value != null) {
                                cell.setCellValue(Integer.parseInt(value.toString()));
                            }
                        } else if (filedType == Double.class
                                || filedType == Float.class) {
                            // 双精度或浮点型
                            if (value != null) {
                                cell.setCellType(CellType.NUMERIC);
                                cell.setCellValue(Double.parseDouble(value.toString()));
                            }
                        } else if (filedType == Date.class) {
                            // 日期
                            if (value != null) {
                                cell.setCellValue(new SimpleDateFormat(StringTools.nvl(excelColumn.format(), TimeFormat.DATETIME.getPattern())).format(value));
                            }
                        }
                        if (k == rowList.size()) {
                            sheet.autoSizeColumn(j, true);
                        }
                    }
                }

            }
        }
        outExcel(out, workbook);
    }

    /**
     * 输出Excel文件,可以用在web项目中:导出Excel
     *
     * @param out  输出流
     * @param book 工作簿
     */
    public static void outExcel(OutputStream out, Workbook book) {
        try {
            out = new BufferedOutputStream(out);
            book.write(out);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    log.error(e.getMessage(), e);
                }
            }
        }
    }

    public static File saveExcel(File file, Workbook book) {
        try {
            outExcel(new FileOutputStream(file), book);
        } catch (FileNotFoundException e) {
            log.error(e.getMessage(), e);
        }
        return file;
    }

    static String getValue(String expression, Object target) {
        if (expression.startsWith("${")) {
            expression = expression.substring(expression.indexOf("${") + 2, expression.indexOf('}'));
        }
        //把pojo对象传入脚本
        StandardEvaluationContext context = new StandardEvaluationContext(target);
        //执行表达式
        Expression result = parser.parseExpression(expression);
        Object eval = result.getValue(context);
        if (ObjectTools.isBlank(eval)) {
            return null;
        }
        if (eval instanceof LocalDateTime) {
            return TimeFormat.toStr((LocalDateTime) eval, TimeFormat.DATETIME);
        } else if (eval instanceof LocalDate) {
            return TimeFormat.toStr((LocalDate) eval, TimeFormat.DATE);
        } else if (eval instanceof String) {
            return (String) eval;
        } else {
            return eval.toString();
        }
    }

    /**
     * 列表输出
     *
     * @param sheet
     * @param startRow 从0开始，表达式开始行
     * @param startCol 从0开始，表达式开始列
     * @param data
     * @throws Exception
     */
    public static void exportListWithExpression(Sheet sheet, int startRow, int startCol, List<?> data) throws Exception {
        check(sheet, data);
        //读取表达式
        Row row = sheet.getRow(startRow);
        if (row != null) {
            Iterator<Cell> cells = row.cellIterator();
            if (ObjectTools.isBlank(data)) {
                //没有值时清除掉模版
                while (cells.hasNext()) {
                    Cell cell = cells.next();
                    cell.setCellValue("");
                }
            } else {
                List<CellStyle> cellStyles = new ArrayList<>();
                List<CellExpression> expressions = new ArrayList<>();
                while (cells.hasNext()) {
                    Cell cell = cells.next();
                    if (cell.getColumnIndex() < startCol) {
                        continue;
                    }
                    cellStyles.add(cell.getCellStyle());
                    String expression = readCellExpression(cell);
                    if (StringTools.isNotBlank(expression) && (expression.startsWith("${") || expression.startsWith("#{")) && expression.endsWith("}")) {
                        expressions.add(new CellExpression(cell.getRowIndex(), cell.getColumnIndex(), expression));
                    } else {
                        expressions.add(null);
                    }
                }
                CellStyle rowStyle = null;
                short height = 15;
                for (int i = 0; i < data.size(); i++) {
                    row = sheet.getRow(startRow + i);
                    if (i == 0) {
                        rowStyle = row.getRowStyle();
                        height = row.getHeight();
                    }
                    if (row == null) {
                        row = sheet.createRow(startRow + i);
                        row.setRowStyle(rowStyle);
                        row.setHeight(height);
                    }
                    Object thisRow = data.get(i);
                    for (int j = 0; j < expressions.size(); j++) {
                        CellExpression expr = expressions.get(j);
                        if (expr == null) {
                            continue;
                        }
                        Cell cell = row.getCell(expr.getColumnIndex());
                        if (cell == null) {
                            cell = row.createCell(expr.getColumnIndex());
                            cell.setCellStyle(cellStyles.get(j));
                        }
                        if (StringTools.isNotBlank(expr.getExpression())) {
                            Object value = getValue(expr.getExpression(), thisRow);
                            if (value != null) {
                                if (ObjectTools.isNotBlank(value) && value.toString().length() == 19) {
                                    if (StringTools.regexMatch(value.toString(), "^[1-9]\\d{3}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])T(20|21|22|23|[0-1]\\d):[0-5]\\d:[0-5]\\d$")) {
                                        value = value.toString().replace("T", " ");
                                    }
                                }
                            }
                            if (value != null) {
                                setCellValue(cell, value);
                            } else {
                                cell.setCellValue("");
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * 列表输出
     *
     * @param sheet
     * @param startRow 从0开始，表达式行
     * @param data
     * @throws Exception
     */
    public static void exportListWithExpression(Sheet sheet, int startRow, List<?> data) throws Exception {
        exportListWithExpression(sheet, startRow, 0, data);
    }

    /**
     * 列表输出
     *
     * @param sheet
     * @param data
     * @throws Exception
     */
    public static void exportListWithExpression(Sheet sheet, List<?> data) throws Exception {
        exportListWithExpression(sheet, 0, 0, data);
    }

    public static void setCellValue(Cell cell, Object value) {
        if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Float) {
            cell.setCellValue((Float) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof LocalDate) {
            cell.setCellValue((LocalDate) value);
        } else if (value instanceof RichTextString) {
            cell.setCellValue((RichTextString) value);
        } else if (value instanceof Calendar) {
            cell.setCellValue((Calendar) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    private static String readCellExpression(Cell cell) {
        CellType cellType = cell.getCellType();
        String expression = "";
        switch (cellType) {
            case BLANK:
            case STRING: {
                expression = cell.getStringCellValue();
                break;
            }
            case NUMERIC: {
                expression = cell.getNumericCellValue() + "";
                break;
            }
            default:
                throw new IllegalStateException("Unexpected value: " + cellType);
        }
        return expression;
    }

    public static void exportObjectWithExpression(Sheet sheet, Object data) throws Exception {
        check(sheet, data);
        Iterator<Row> rows = sheet.rowIterator();
        while (rows.hasNext()) {
            Row row = rows.next();
            Iterator<Cell> cells = row.cellIterator();
            while (cells.hasNext()) {
                Cell cell = cells.next();
                String expression = readCellExpression(cell);
                if (StringTools.isNotBlank(expression) && expression.startsWith("${") && expression.endsWith("}")) {
                    String value = getValue(expression, data);
                    cell.setCellValue(value);
                }
            }
        }
    }

    private static void check(Sheet sheet, Object data) {
        if (sheet == null || ObjectTools.isBlank(data)) {
            log.error("工作簿和数据为空");
        }
    }

    /**
     * 插入行
     *
     * @param sheet
     * @param insertRowNum
     * @return
     */
    public static Row insertRow(Sheet sheet, int insertRowNum) {
        sheet.shiftRows(insertRowNum, sheet.getLastRowNum(), 1, true, false);
        return sheet.createRow(insertRowNum);
    }

}
